package com.starbaba.stat.service.impl;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import com.base.common.database.BaseDao;
import com.starbaba.core.web.page.PageResponse;
import com.starbaba.stat.model.StatFlowConverModel;
import com.starbaba.stat.service.StatFlowConverService;
import com.starbaba.stat.utils.Constant;

@SuppressWarnings("rawtypes")
@Component
public class StatFlowConverServiceImpl implements StatFlowConverService{

	private static BaseDao dao = new BaseDao(Constant.LOAN_STAT_DB_POOL);// Constant.LOAN_STAT_DB_POOL
	
	@Override
	public void showPage(Map<String, Object> paramsMap) throws Exception {
		
		List<StatFlowConverModel> prdList = dao.list("SELECT t.prd_id, b.name prd_name FROM loan.comm_product_prdid b,(SELECT a.prd_id FROM stat.stat_flow_conversion_click_form a GROUP BY a.prd_id order by null ) t WHERE b.prdid = t.prd_id ", StatFlowConverModel.class);
		List<StatFlowConverModel> channelList = dao.list("select channel_name,channel_code channel from loan.comm_product_channel  ", StatFlowConverModel.class);
		List<StatFlowConverModel> activityList = dao.list("SELECT f.functionid functionid, f.functionid functionid_name FROM stat.stat_flow_conversion_click_form f GROUP BY f.functionid  order by null ", StatFlowConverModel.class);
		List<StatFlowConverModel> pageList = dao.list("select page page, page page_name from stat_flow_conversion_click_form group by page", StatFlowConverModel.class);
		List<StatFlowConverModel> ckmoduleList = dao.list("SELECT ck_module ck_module, ck_module ck_module_name FROM stat.stat_flow_conversion_click_form GROUP BY ck_module order by null  ", StatFlowConverModel.class);
		List<StatFlowConverModel> positionList = dao.list("select position position, position position_name from stat_flow_conversion_click_form GROUP BY position order by null ", StatFlowConverModel.class);
		
		StatFlowConverModel all = new StatFlowConverModel("all", "全部");
		prdList.add(0, all);
		channelList.add(0, all);
		activityList.add(0, all);
		pageList.add(0, all);
		ckmoduleList.add(0, all);
		positionList.add(0, all);
		
		paramsMap.put("prdList", prdList);
		paramsMap.put("channelList", channelList);
		paramsMap.put("activityList", activityList);
		paramsMap.put("pageList", pageList);
		paramsMap.put("ckmoduleList", ckmoduleList);
		paramsMap.put("positionList", positionList);
		
	}

	@Override
	public List getList(Integer start, Integer length, PageResponse pageResponse, Map<String, Object> paramsMap) throws Exception {
		
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String platform = (String) paramsMap.get("platform");
		String prd_id = (String) paramsMap.get("prd_id");
		String channel = (String) paramsMap.get("channel");
		String user_type = (String) paramsMap.get("user_type");
		String activity = (String) paramsMap.get("activity");
		String page = (String) paramsMap.get("page");
		String ck_module = (String) paramsMap.get("ck_module");
		String position = (String) paramsMap.get("position");
		
		StringBuilder where = new StringBuilder(" where 1 = 1 ");
		if (StringUtils.isNotEmpty(startTime)) {
			where.append("and time >= '").append(startTime).append("' ");
		}
		if (StringUtils.isNotEmpty(endTime)) {
			where.append("and time <= '").append(endTime).append("' ");
		}
		if (StringUtils.isNotEmpty(platform) && !"all".equals(platform)) {
			where.append("and platform = '").append(platform).append("' ");
		}
		if (StringUtils.isNotEmpty(prd_id) && !"all".equals(prd_id)) {
			where.append("and prd_id = '").append(prd_id).append("' ");
		}
		if (StringUtils.isNotEmpty(channel) && !"all".equals(channel)) {
			where.append("and channel = '").append(channel).append("' ");
		}
		if (StringUtils.isNotEmpty(user_type) && "total".equals(user_type)) {
			where.append("and user_type = 'total' ");
		} else if (StringUtils.isNotEmpty(user_type)) {
			where.append("and user_type = '" + user_type + "' and user_type <> 'total' ");
		} else {
			where.append("and user_type <> 'total' ");
		}
		if (StringUtils.isNotEmpty(activity) && !"all".equals(activity)) {
			where.append("and functionid = '").append(activity).append("' ");
		}
		if (StringUtils.isNotEmpty(page) && !"all".equals(page)) {
			where.append("and page = '").append(page).append("' ");
		}
		if (StringUtils.isNotEmpty(ck_module) && !"all".equals(ck_module)) {
			where.append("and ck_module = '").append(ck_module).append("' ");
		}
		if (StringUtils.isNotEmpty(position) && !"all".equals(position)) {
			where.append("and position = '").append(position).append("' ");
		}
		
		StringBuilder groupby = new StringBuilder(" group by time, user_type, ");
		if (!"all".equals(platform)) {
			groupby.append("platform, ");
		}
		if (!"all".equals(prd_id)) {
			groupby.append("prd_id, ");
		}
		if (!"all".equals(channel)) {
			groupby.append("channel, ");
		}
		if (!"all".equals(activity)) {
			groupby.append("functionid, ");
		}
		if (!"all".equals(page)) {
			groupby.append("page, ");
		}
		if (!"all".equals(ck_module)) {
			groupby.append("ck_module, ");
		}
		if (!"all".equals(position)) {
			groupby.append("position, ");
		}
		String groupByString = groupby.toString().substring(0, groupby.length() - 2) + " ";
		
		StringBuilder sqlList = new StringBuilder("select time, platform, prd_id, cpp.name prd_name, channel, cpc.channel_name channel_name, user_type, ");
		sqlList.append("page page_name, ck_module ck_module_name, position position_name, functionid functionid_name, contentid, ");
		sqlList.append("sum(click_uv) click_uv, sum(click_pv) click_pv, sum(join_uv) join_uv, sum(join_pv) join_pv ");
		sqlList.append("from stat_flow_conversion_click_form a left join loan.comm_product_prdid cpp on a.prd_id = cpp.prdid ");
		sqlList.append("left join loan.comm_product_channel cpc on a.channel = cpc.channel_code ");
		sqlList.append(where).append(groupByString).append("order by time desc ");
		String sqlCount = "select count(1) from (" + sqlList.toString() + ") z ";
		
		if (start != null && length != null) {
			sqlList.append("limit " + start + ", " + length);
		}
		
		int count = dao.getInt(sqlCount);
		List<StatFlowConverModel> list = dao.list(sqlList.toString(), StatFlowConverModel.class);
		for (StatFlowConverModel model : list) {
			model.setConver_rate(getPercent(model.getJoin_uv(), model.getClick_uv()));
			if ("total".equals(model.getUser_type())) {
				model.setUser_type("全部");
			} else if ("new".equals(model.getUser_type())) {
				model.setUser_type("新用户");
			} else if ("old".equals(model.getUser_type())) {
				model.setUser_type("老用户");
			}
			if ("all".equals(platform)) {
				model.setPlatform("全部");
			} 
			if ("all".equals(prd_id)) {
				model.setPrd_name("全部");
			}
			if ("all".equals(channel)) {
				model.setChannel_name("全部");
			}
			
			if ("all".equals(activity)) {
				model.setFunctionid_name("全部");
			}
			if ("all".equals(page)) {
				model.setPage_name("全部");
			}
			if ("all".equals(ck_module)) {
				model.setCk_module_name("全部");
			}
			if ("all".equals(position)) {
				model.setPosition_name("全部");
			}
			if (StringUtils.isEmpty(model.getChannel_name())) {
				model.setChannel_name(model.getChannel());
			}
			
		}
		pageResponse.setITotalRecords(count);
		pageResponse.setITotalDisplayRecords(count);
		
		return list;
	}

	public static String getPercent(Long a, Long b) {
		return (float) Math.round((float) a / b * 10000) / 100 + "%";
	}
	
	public static String getPercent(String a, String b) {
		if (StringUtils.isEmpty(a) || StringUtils.isEmpty(b)){
			return "--";
		}
		Long al = Long.parseLong(a);
		Long bl = Long.parseLong(b);

		return (float) Math.round((float) al / bl * 10000) / 100 + "%";
	}
	
}
